﻿Imports System.Data
Imports System.Collections.Generic
Imports System.Web
Imports System.Web.Security
Imports System.Data.SqlClient

''' <summary>
''' clsAuth 的摘要描述
''' </summary>
Partial Public Class clsAuth
    '
    ' TODO: 在此加入建構函式的程式碼
    '
    Public Sub New()
    End Sub
    ''' <summary>
    ''' 重新計算全部單位之樹狀代碼
    ''' </summary>
    ''' <remarks></remarks>
    Public Shared Sub BuildOrganizationCode(ByVal dbName As String)
        BuildOrganizationCode("null", "", dbName)
    End Sub
    Private Shared Sub BuildOrganizationCode(ByVal parent As String, ByVal organizationCode As String, ByVal dbName As String)

        Dim sql As New StringBuilder
        Dim conn = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings(dbName).ConnectionString)
        sql.Length = 0

        'SQL2000 沒有 row_number() function, 改用 temp table
        sql.AppendFormat("create table #a (OrganizationID int, OrganizationCode int identity) ")
        sql.AppendFormat("insert #a (OrganizationID) ")
        sql.AppendFormat("select OrganizationID from Organization ")

        ' Root
        If parent = "null" Then
            sql.AppendFormat("where Parent is null ", parent)
        Else
            sql.AppendFormat("where Parent = {0} ", parent)
        End If

        sql.AppendFormat("order by OrganizationID ")

        sql.AppendFormat("update o set ")
        sql.AppendFormat("OrganizationCode = '{0}' + right('0'+cast(o_code.OrganizationCode as nvarchar(10)), 2) ", organizationCode)
        sql.AppendFormat("from Organization o ")
        sql.AppendFormat("inner join ( ")


        sql.AppendFormat("select OrganizationID ")
        sql.AppendFormat(", OrganizationCode ")
        sql.AppendFormat("from #a) o_code on o.OrganizationID = o_code.OrganizationID ")



        'SQL2000 沒有 row_number() function, 改用 temp table
        sql.AppendFormat("drop table #a ")

        SqlHelper.ExecuteNonQuery(conn, Data.CommandType.Text, sql.ToString)

        sql.Length = 0
        sql.AppendFormat("select o.* ")
        sql.AppendFormat("from Organization o ")
        sql.AppendFormat("where 1=1 ")

        ' Root
        If parent = "null" Then
            sql.AppendFormat("and o.Parent is null ")
        Else
            sql.AppendFormat("and o.Parent = {0} ", parent)
        End If

        Dim dt As Data.DataTable = SqlHelper.ExecuteDataset(conn, Data.CommandType.Text, sql.ToString()).Tables(0)

        For Each dr As Data.DataRow In dt.Rows
            BuildOrganizationCode(dr("OrganizationID"), dr("OrganizationCode"), dbName)
        Next

    End Sub
    
End Class


